const express = require("express");
const router = express.Router();
const db = require("../db/index.js");
router.get("/getStudents",(req,res)=>{
    let offsetNum2 = req.query.limit || 10;
    let offsetNum1 = (req.query.page-1)*offsetNum2 || 0;
    const p = new Promise((resolve,reject)=>{
        const sqlCount = "select count(*) as total  from db_students where status <> 4";
        db.query(sqlCount,(err,result)=>{
            if(err)  reject(err);
            resolve(result)
        })
    })
    p.then((value)=>{
        let count = value[0].total;
        const sql = `select * from db_students where status <> 4 limit ${offsetNum1} , ${offsetNum2}   `;
        db.query(sql,(err,result)=>{
            if(err) res.cc(err);
            res.send(JSON.stringify({
                code:0,
                msg:"查询成功",
                data:result,
                count
            }))
        })
    }).catch((reason)=>{
        res.cc(reason)
    })
})

router.get("/getDelStudents",(req,res)=>{
    let offsetNum2 = req.query.limit || 10;
    let offsetNum1 = (req.query.page-1)*offsetNum2 || 0;
    const p = new Promise((resolve,reject)=>{
        const sqlCount = "select count(*) as total  from db_students where status = 4";
        db.query(sqlCount,(err,result)=>{
            if(err)  reject(err);
            resolve(result)
        })
    })
    p.then((value)=>{
        let count = value[0].total;
        const sql = `select * from db_students where status = 4 limit ${offsetNum1} , ${offsetNum2} `;
        db.query(sql,(err,result)=>{
            if(err) res.cc(err);
            res.send(JSON.stringify({
                code:0,
                msg:"查询成功",
                data:result,
                count
            }))
        })
    }).catch((reason)=>{
        res.cc(reason)
    })
})

router.get("/delStudents",(req,res)=>{
    const id = Number.parseInt(req.query.id);
    const sql = "update db_students set status = 4 where id = ?";
    db.query(sql,id,(err,result)=>{
        if(err) return res.cc(err);
        if(result.affectedRows==1){
            res.send({
                code:0,
                msg:"删除成功"
            })
        }
    })

})


router.get("/oneStudent",(req,res)=>{
    const id = req.query.id;
    const sql = "select * from db_students where id = ? ";
    db.query(sql,id,(err,result)=>{
        if(err) return res.cc(err);
        res.send({
            code:0,
            msg:"查询成功",
            data:result
        })
    })

})

router.post("/updateStudent",(req,res)=>{
    const userinfo = req.body;
    const message = {
        id:Number(userinfo.id),
        name:userinfo.name,
        sex:userinfo.sex,
        age:userinfo.age,
        status:Number(userinfo.status)
    };
    const sql = "update db_students set name = ?,sex = ?,age =?,status=? where id = ?";
    db.query(sql,[message.name,message.sex,message.age,message.status,message.id],(err,result)=>{
        if(err){
            return res.cc(err);
        }
        res.send({
            code:0,
            msg:"修改成功"
        })
    })
})


router.post("/addStudent",(req,res)=>{
    const userinfo = req.body;
    const message = {
        id:null,
        name:userinfo.name,
        sex:userinfo.sex,
        age:userinfo.age,
        status:Number(userinfo.status)
    };
    const sql = "insert into db_students  set ?";
    db.query(sql,message,(err,result)=>{
        if(err){
            return res.cc(err);
        }
        res.send({
            code:0,
            msg:"添加成功"
        })
    })
})

router.get("/searchStudent",(req,res)=>{
    let offsetNum2 = req.query.limit || 10;
    let offsetNum1 = (req.query.page-1)*offsetNum2 || 0;

    const sql = `select * from db_students where  name = '${req.query.name}' and status != 4 limit ${offsetNum1} , ${offsetNum2}`;
    console.log(sql);
    
    db.query(sql,(err,result)=>{
        if(err){ return res.cc(err)};
        res.send(JSON.stringify({
            code:0,
            msg:"查询成功",
            count:result.length,
            data:result
        }))
    })
})


module.exports = router;